'''
This program took a local amazon music database and wrote it to the Amazon RDS database
'''

import os
import sys
import random
import config as c

home = os.path.split(os.getcwd())[0]
sqla_dir = os.path.join(home,'sqlalchemy','lib')
sys.path = [sqla_dir] + sys.path

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from sqlalchemy import Column, Integer, String #, Boolean, ForeignKey
#from sqlalchemy.orm import relationship, backref 
#from sqlalchemy.sql import select,join,and_
from sqlalchemy.schema import UniqueConstraint
from sqlalchemy.exc import IntegrityError, OperationalError

Base = declarative_base()

class Song(Base):
    __tablename__ = 'songs'
    id = Column(Integer, primary_key=True)
    artist = Column(String)
    album = Column(String)
    title = Column(String)
    uri = Column(String)
    album_art = Column(String)
    __table_args__ = (UniqueConstraint('artist', 'album', 'title'),)
    
    def __repr__(self):
        return "<artist={}, album={}, title={}, uri={}>".format(self.artist, self.album, self.title, self.uri)

#engine = create_engine('sqlite:///amazon_music.db', echo=True)
engine = create_engine('postgresql+psycopg2://slzatz:duster00@sonos-companion.cbokdncjyu1p.us-east-1.rds.amazonaws.com:5432/music', echo=True)
#Base.metadata.create_all(engine) # only creates tables if they don't exist
#conn = engine.connect()
Session = sessionmaker(bind=engine)
session = Session()

aws_engine = create_engine('postgresql+psycopg2://slzatz:duster00@mylistmanager.cbokdncjyu1p.us-east-1.rds.amazonaws.com:5432/music') 
#Base.metadata.create_all(aws_engine) # only creates tables if they don't exist
#aws_conn = aws_engine.connect()
aws_Session = sessionmaker(bind=aws_engine)
aws_session = aws_Session()

if __name__ == '__main__':

    rows = session.query(Song).count()
    print "rows = ", rows

    n=0
    songs = session.query(Song) 
    for song in songs:
        print song.id
        print song.artist.encode('ascii', 'ignore')
        print song.album.encode('ascii', 'ignore')
        print song.title.encode('ascii', 'ignore')
        print song.uri.encode('ascii', 'ignore')
        print song.album_art.encode('ascii', 'ignore')
        i = song.uri.find('amz')
        ii = song.uri.find('.')
        id_ = song.uri[i:ii]
        print id_.encode('ascii', 'ignore')
        print "---------------------------------------------------------------"

        try:
            s = Song(artist=song.artist, title=song.title, album=song.album, album_art=song.album_art, uri=song.uri)
            aws_session.add(s)
            aws_session.commit()
        except (IntegrityError, OperationalError) as e:
            aws_session.rollback()
            print "IntegrityError: ",e
        else:
            try:
                print u"{} {} {} {} added to db".format(song.artist, song.title, song.album, song.uri)
            except UnicodeEncodeError as e:
                print "UnicodeEncodeError: ",e

        n+=1


    print n, "songs copied"
